前言

如同前一篇提到，假設今天接到一個DB有一張表裏面資料量蠻大的，每天大概有20萬筆，且這張表要保留至少半年的資料，平常搜尋大多是以一天為條件，所以我們希望搜尋的時候可以只用到某個 partition 區塊就好。所以就打算在既有的 TABLE 上建立 partition。

流程

先建立測試資料

• 從2020-01-01 ~ 2020-04-01 每天塞3筆資料進去
• PK: uid 且有自增長

-- 建立表格
drop table if exists tb1;
CREATE TABLE tb1 (
    uid int NOT NULL AUTO_INCREMENT,
    uname varchar(50),
    create_time timestamp not null default current_timestamp,
    PRIMARY KEY(uid),
    KEY (uname)
);

-- 塞入2020-01-01 到 2020-04-01  每天3筆資料
CREATE TEMPORARY TABLE IF NOT EXISTS `name_list` (
    `name` varchar(10) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `name_list` VALUES ('test1'),('test2'),('test3');

INSERT INTO tb1(uname,create_time)
WITH RECURSIVE
cte  
AS (
      SELECT "2020-01-01 05:00:00" as `expiration`
      UNION ALL
      SELECT
          date_add(`expiration`, INTERVAL 1 DAY) as `expiration`
      FROM cte
      WHERE `expiration` < "2020-04-01 05:00:00"
    ),
cte1
AS
    (
    SELECT * FROM cte
    CROSS JOIN `name_list`
    )
SELECT     
    `name`
    ,`expiration`
FROM cte1

★ 假設我覺得這張表很大，想要在這張表切 partition ，且以日來切，步驟如下

1. 因為切 partition ，切的條件必須放在 PK中，這裡我們想利用 create_time 這個欄位來切，所以PK就要修改。所以第一步要先 drop PK

ALTER TABLE tb1 DROP PRIMARY KEY;

2. 上面這個步驟執行就發現錯誤
   1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
   所以我們需要先把自增長拿掉

ALTER TABLE tb1 modify column uid INT NOT NULL; 

3. 接著就可以 drop PK
4. 重新建立 PK，並把要切 partition 的欄位放進去

ALTER TABLE tb1 ADD PRIMARY KEY(`uid`,`create_time`);

5. 把原本的自增長欄位加回去

ALTER TABLE tb1 MODIFY uid INT  NOT NULL AUTO_INCREMENT;  

6. 這裡因文章範圍有限，我只切從2020-03-23 ~ 2020-04-02

ALTER TABLE tb1 partition by range(unix_timestamp(create_time))
(
 partition ym_200322 values less than (unix_timestamp('2020-03-23 00:00:00')), 
 partition ym_200323 values less than (unix_timestamp('2020-03-24 00:00:00')), 
 partition ym_200324 values less than (unix_timestamp('2020-03-25 00:00:00')), 
 partition ym_200325 values less than (unix_timestamp('2020-03-26 00:00:00')), 
 partition ym_200326 values less than (unix_timestamp('2020-03-27 00:00:00')), 
 partition ym_200327 values less than (unix_timestamp('2020-03-28 00:00:00')), 
 partition ym_200328 values less than (unix_timestamp('2020-03-29 00:00:00')), 
 partition ym_200329 values less than (unix_timestamp('2020-03-30 00:00:00')), 
 partition ym_200330 values less than (unix_timestamp('2020-03-31 00:00:00')), 
 partition ym_200331 values less than (unix_timestamp('2020-04-01 00:00:00')),
 partition ym_200401 values less than (unix_timestamp('2020-04-02 00:00:00'))
);  

7. 檢查是否有進到 partition：利用下面的語法可以觀察每個 partition 裡面的數量如下圖1

select table_schema, table_name, partition_name,partition_ordinal_position,partition_method, partition_description, table_rows
from information_schema.partitions
where table_schema = 'test' and table_name = 'tb1'

從下圖可以看到

• 因為我只從2020-03-23 開始有切 partition ，所以小於此日期的都會在這裡。
• 後面就是每天有3筆資料。
  
  圖1

小結

以上為如何在原本既有的 TABLE 上建立 partition。幾個重點整理一下

1. 由於要拿來切 partition 的欄位必須在PK中，所以PK勢必得重建
2. 如果要 drop PK 就要先把自增長欄位先拿掉
3. 可以透過系統表來觀察資料是否有寫入到對應的 partition，如圖1，藉此檢查是否有符合自己的期望

下一篇再分享關於切的 partition 數量跟效能有什麼關係，可以無限制的切下去嗎?

資料庫知識相當廣泛，文中若有不正確的地方，也煩請各位大神不吝指教，謝謝